/*
CREATED		2013/9/13
MODIFIED		2013/9/13
PROJECT		
MODEL			
COMPANY		
AUTHOR		
VERSION		
DATABASE		MS SQL 2005 
*/



















CREATE TABLE [HR_BRANCH]
(
	[BRANCH_CODE] VARCHAR(20) NOT NULL,
	[BRANCH_NAME_CHI] NVARCHAR(50) NOT NULL,
	[BRANCH_NAME_ENG] VARCHAR(100) NULL,
	[BRANCH_CREATEDATE] DATETIME NULL,
	[BRANCH_STATUS] VARCHAR(10) NULL,
CONSTRAINT [PK_HR_BRANCH] PRIMARY KEY ([BRANCH_CODE])
) 
GO

CREATE TABLE [HR_STAFF]
(
	[STAFF_CODE] VARCHAR(20) NOT NULL,
	[BRANCH_CODE] VARCHAR(20) NULL,
	[TITLE_CODE] VARCHAR(20) NULL,
	[STAFF_NAME_CHI] VARCHAR(20) NOT NULL,
	[STAFF_NAME_ENG] VARCHAR(100) NOT NULL,
	[STAFF_EMAILADDRESS] VARCHAR(50) NULL,
	[STAFF_DATECREATE] DATETIME NULL,
	[STAFF_DATELEAVE] DATETIME NULL,
CONSTRAINT [PK_HR_STAFF] PRIMARY KEY ([STAFF_CODE])
) 
GO

CREATE TABLE [HR_STAFF_TITLE]
(
	[TITLE_CODE] VARCHAR(20) NOT NULL,
	[TITLE_NAME_CHI] VARCHAR(50) NOT NULL,
	[TITLE_NAME_ENG] VARCHAR(100) NULL,
	[TITLE_PRIORITY] INTEGER DEFAULT 0 NULL,
	[TITLE_EXP_DATA] VARCHAR(200) NULL,
CONSTRAINT [PK_HR_STAFF_TITLE] PRIMARY KEY ([TITLE_CODE])
) 
GO

CREATE TABLE [HR_ROSTERS]
(
	[ROSTER_ID] INTEGER IDENTITY(1,1) NOT NULL,
	[BRANCH_CODE] VARCHAR(20) NOT NULL,
	[STAFF_CODE] VARCHAR(20) NOT NULL,
	[ROSTER_DATE] DATETIME NOT NULL,
	[BRANCHSHIFT_CODE] VARCHAR(10) NOT NULL,
	[ROSTER_QUEUEPOS] INTEGER DEFAULT 0 NOT NULL,
	[ROSTER_SHIFTLOCK] SMALLINT DEFAULT 0 NOT NULL,
CONSTRAINT [PK_HR_ROSTERS] PRIMARY KEY ([ROSTER_ID])
) 
GO

CREATE TABLE [HR_PUBLICHOLIDAY]
(
	[PUBLICHOLIDAY_CODE] VARCHAR(20) NOT NULL,
	[PUBLICHOLIDAY_DATE] DATETIME NOT NULL, UNIQUE ([PUBLICHOLIDAY_DATE]),
	[PUBLICHOLIDAY_NAME_CHI] VARCHAR(50) NOT NULL,
	[PUBLICHOLIDAY_NAME_ENG] VARCHAR(100) NULL,
CONSTRAINT [PK_HR_PUBLICHOLIDAY] PRIMARY KEY ([PUBLICHOLIDAY_CODE])
) 
GO

CREATE TABLE [HR_PUBLICHOLIDAYSTAFF]
(
	[STAFF_CODE] VARCHAR(20) NOT NULL,
	[PUBLICHOLIDAY_CODE] VARCHAR(20) NOT NULL,
	[PUBLICHOLIDAY_DATE] DATETIME NULL,
	[STAFF_USE_DATE] DATETIME NULL,
	[REQUEST_FLAG] SMALLINT DEFAULT 0 NULL,
CONSTRAINT [PK_HR_PUBLICHOLIDAYSTAFF] PRIMARY KEY ([STAFF_CODE],[PUBLICHOLIDAY_CODE])
) 
GO

CREATE TABLE [HR_STAFFSHIFTSHOP]
(
	[STAFFSHIFTSHOP_ID] INTEGER IDENTITY(1,1) NOT NULL,
	[STAFF_CODE] VARCHAR(20) NOT NULL,
	[BRANCH_CODE] VARCHAR(20) NOT NULL,
	[START_SHIFT] DATETIME NOT NULL,
	[END_SHIFT] DATETIME NOT NULL,
CONSTRAINT [PK_HR_STAFFSHIFTSHOP] PRIMARY KEY ([STAFFSHIFTSHOP_ID])
) 
GO

CREATE TABLE [HR_BRANCHSHIFT]
(
	[BRANCHSHIFT_CODE] VARCHAR(10) NOT NULL,
	[BRANCH_CODE] VARCHAR(20) NOT NULL,
	[BRANCHSHIFT_SHIFTNAME] VARCHAR(20) NOT NULL,
	[BRANCHSHIFT_STARTTIME] CHAR(4) NOT NULL,
	[BRANCHSHIFT_ENDTIME] CHAR(4) NOT NULL,
	[BRANCHSHIFT_STARTDATE] DATETIME NOT NULL,
	[BRANCHSHIFT_ENDDATE] DATETIME NULL,
CONSTRAINT [PK_HR_BRANCHSHIFT] PRIMARY KEY ([BRANCHSHIFT_CODE])
) 
GO















ALTER TABLE [HR_STAFF] ADD CONSTRAINT [FK_REF_STAFF_BRANCH] FOREIGN KEY([BRANCH_CODE]) REFERENCES [HR_BRANCH] ([BRANCH_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE [HR_ROSTERS] ADD CONSTRAINT [FK_ROSTER_BRANCH] FOREIGN KEY([BRANCH_CODE]) REFERENCES [HR_BRANCH] ([BRANCH_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE [HR_STAFFSHIFTSHOP] ADD CONSTRAINT [FK_STAFFSHIFTSHOP_SHOP] FOREIGN KEY([BRANCH_CODE]) REFERENCES [HR_BRANCH] ([BRANCH_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE [HR_BRANCHSHIFT] ADD CONSTRAINT [FK_BRANCHSHIFT_BRANCH] FOREIGN KEY([BRANCH_CODE]) REFERENCES [HR_BRANCH] ([BRANCH_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE [HR_PUBLICHOLIDAYSTAFF] ADD CONSTRAINT [FK_PUBLICHOLIDAYSTAFF_S] FOREIGN KEY([STAFF_CODE]) REFERENCES [HR_STAFF] ([STAFF_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE [HR_ROSTERS] ADD CONSTRAINT [FK_ROSTER_STAFF] FOREIGN KEY([STAFF_CODE]) REFERENCES [HR_STAFF] ([STAFF_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE [HR_STAFFSHIFTSHOP] ADD CONSTRAINT [FK_STAFFSHIFTSHOP_STAFF] FOREIGN KEY([STAFF_CODE]) REFERENCES [HR_STAFF] ([STAFF_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE [HR_STAFF] ADD CONSTRAINT [FK_REF_STAFF_TITLE] FOREIGN KEY([TITLE_CODE]) REFERENCES [HR_STAFF_TITLE] ([TITLE_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE [HR_PUBLICHOLIDAYSTAFF] ADD CONSTRAINT [FK_PUBLICHOLIDAYSTAFF_P] FOREIGN KEY([PUBLICHOLIDAY_CODE]) REFERENCES [HR_PUBLICHOLIDAY] ([PUBLICHOLIDAY_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE [HR_ROSTERS] ADD CONSTRAINT [FK_ROSTER_BRANCHSHIFT] FOREIGN KEY([BRANCHSHIFT_CODE]) REFERENCES [HR_BRANCHSHIFT] ([BRANCHSHIFT_CODE])  ON UPDATE NO ACTION ON DELETE NO ACTION 
GO


SET QUOTED_IDENTIFIER ON
GO









SET QUOTED_IDENTIFIER OFF
GO





